/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBAccess;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Nguyen Kim Manh
 */
public class DB_Connect {

    static ArrayList data;

    private static void getData() {

        String fileName = "SQL_config.txt";
        data = new ArrayList();
        BufferedReader read = null;
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(fileName);
            read = new BufferedReader(new InputStreamReader(fis));
            String line = read.readLine();
            while (line != null) {
                String dt[] = line.split(":");
                data.add(dt[1].trim());
                line = read.readLine();
            }


        } catch (Exception e) {
        }
    }

    public static Connection getConnection() throws ClassNotFoundException, SQLException {
            Connection cnn = null;
      
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            //get data from SQL_config.txt
            getData();
            String serverIP = data.get(0).toString();
            String port = data.get(1).toString();
            String user = data.get(2).toString();
            String password = data.get(3).toString();

            cnn = DriverManager.getConnection("jdbc:sqlserver://" + serverIP + ":" + port + ";databaseName=Wonder_Lower_Price", user, password);

       
        return cnn;
    }
    

    public static void closeConnection(Connection cnn) {
        if (cnn != null) {
            try {
                cnn.close();
            } catch (SQLException ex) {
                Logger.getLogger(DB_Connect.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
    
     public static int executeNonQuery(String storeProcedure, ArrayList<String> listParam) throws SQLException, ClassNotFoundException {
        int kq = -1;
        Connection conn = getConnection();

        CallableStatement clb = conn.prepareCall(storeProcedure);
        if (listParam != null) {
            for (int i = 0; i < listParam.size(); i++) {
                clb.setString(i + 1, listParam.get(i));
            }
        }

        kq = clb.executeUpdate();
        closeConnection(conn);

        return kq;
    }

    public static ResultSet executeQuery(String storeProcedure, ArrayList<String> listParam) throws ClassNotFoundException, SQLException {
        ResultSet rs = null;

        Connection conn = getConnection();
        CallableStatement clb = conn.prepareCall(storeProcedure);
        if (listParam != null) {
            for (int i = 0; i < listParam.size(); i++) {
                clb.setString(i + 1, listParam.get(i));
            }
        }

        rs = clb.executeQuery();

//        closeConnection(conn);
        return rs;
    }

    public static int executeNonQueryPreparedStatement(String sql, ArrayList<String> listParam) throws SQLException, ClassNotFoundException {
        int kq = -1;
        Connection conn = getConnection();

        PreparedStatement pr = conn.prepareStatement(sql);

        if (listParam != null) {
            for (int i = 0; i < listParam.size(); i++) {
                pr.setString(i + 1, listParam.get(i));
            }
        }

        kq = pr.executeUpdate();
        closeConnection(conn);

        return kq;
    }

    public static ResultSet executeQueryPreparedStatement(String sql, ArrayList<String> listParam) throws ClassNotFoundException, SQLException {
        ResultSet rs = null;

        Connection conn = getConnection();
        PreparedStatement pr = conn.prepareStatement(sql);
        if (listParam != null) {
            for (int i = 0; i < listParam.size(); i++) {
                pr.setString(i + 1, listParam.get(i));
            }
        }

        rs = pr.executeQuery();

//        closeConnection(conn);
        return rs;
    }
}
